using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;

/// <summary>
/// Summary description for clsDataObject
/// </summary>
public class clsDataObject
{
    String m_strConnectionString;
    String m_strDatabase;
    String m_strConnectionType;
    Boolean m_bKeepOpen;
    IDbConnection m_idbConnection;

    #region Constructors

        public clsDataObject()
        {
            m_bKeepOpen = false;
            m_strConnectionString = ConfigurationManager.AppSettings.Get("ConnectionString");
            m_strDatabase = ConfigurationManager.AppSettings.Get("Database");
            m_strConnectionType = ConfigurationManager.AppSettings.Get("ConnectionType");
        }

        public clsDataObject(Boolean bKeepOpen)
        {
            m_bKeepOpen = bKeepOpen;
            m_strConnectionString = ConfigurationManager.AppSettings.Get("ConnectionString");
            m_strDatabase = ConfigurationManager.AppSettings.Get("Database");
            m_strConnectionType = ConfigurationManager.AppSettings.Get("ConnectionType");
        }

        public clsDataObject(String ConnectionString, String Database, String ConnectionType)
        {
            m_bKeepOpen = false;
            m_strConnectionString = ConnectionString;
            m_strDatabase = Database;
            m_strConnectionType = ConnectionType;
        }

        public clsDataObject(String ConnectionString, String Database, String ConnectionType, Boolean bKeepOpen)
        {
            m_bKeepOpen = bKeepOpen;
            m_strConnectionString = ConnectionString;
            m_strDatabase = Database;
            m_strConnectionType = ConnectionType;
        }
    #endregion

    #region Properties
        private IDbConnection cnConnection
        {
            get
            {
                if (m_bKeepOpen)
                {
                    if (m_idbConnection == null)
                    {
                        m_idbConnection = ConnectionFactory();
                    }
                    return m_idbConnection;
                }
                else
                {
                    return ConnectionFactory();
                }
                
            }
        }
    #endregion

    #region Public methods

        public int Execute(String strSQL)
        {
            int iReturn = 0;
            IDbConnection myConnection;
            IDbCommand myCommand = CommandFactory();

            myConnection = cnConnection;
            
            myCommand.Connection = myConnection;
            myCommand.CommandText = strSQL;
            if (!m_bKeepOpen)
                myConnection.Open();
            else
            {
                if (myConnection.State == ConnectionState.Closed)
                    myConnection.Open();
            }
            iReturn = myCommand.ExecuteNonQuery();
            if (!m_bKeepOpen)
            {
                myConnection.Close();
                myConnection.Dispose();
            }
            return iReturn;
        }

        public object ExecuteScalar(String strSQL)
        {
            object objReturn;
            IDbConnection myConnection;
            IDbCommand myCommand = CommandFactory();

            myConnection = cnConnection;

            myCommand.Connection = myConnection;
            myCommand.CommandText = strSQL;

            if (!m_bKeepOpen)
                myConnection.Open();
            else
            {
                if (myConnection.State == ConnectionState.Closed)
                    myConnection.Open();
            }

            objReturn = myCommand.ExecuteScalar();
            if (!m_bKeepOpen)
            {
                myConnection.Close();
                myConnection.Dispose();
            }
            return objReturn;
        }

        public DataSet GetData(String Table, String SQL)
        {
            DataSet dtsReturn = new DataSet();
            IDbConnection idbConnection = cnConnection;
            IDbDataAdapter idbAdapter = DataAdapterFactory(SQL, idbConnection);
            IDbCommand idbCommand = CommandFactory();
            idbCommand.Connection = idbConnection;
            idbCommand.CommandText = SQL;
            if (!m_bKeepOpen)
                idbConnection.Open();
            else
            {
                if (idbConnection.State == ConnectionState.Closed)
                    idbConnection.Open();
            }
            idbAdapter.SelectCommand = idbCommand;
            idbAdapter.Fill(dtsReturn);
            if (!m_bKeepOpen)
            {
                idbConnection.Close();
                idbConnection.Dispose();
            }
            dtsReturn.Tables[0].TableName = Table;            
            return dtsReturn;
        }

        public void Close()
        {
            cnConnection.Close();
            cnConnection.Dispose();
        }

    #endregion
        
    public int GetNumberOfRecords(String strSQL)
    {
        DataSet dtsData = new DataSet();
        int iNumber;

        dtsData = DataConnection.GetDataObject().GetData("Data",strSQL);

        iNumber = dtsData.Tables["Data"].Rows.Count;


        return iNumber;
    }
    
    public String GetValue(String TableName, String FilterField, DataType DataType, String FilterValue, String ReturnField)
    {
        String strSQL, strPrefix, strSuffix;
        DataSet myDataSet = new DataSet();
        IDbConnection myConnection  = ConnectionFactory();
        IDbDataAdapter myDataAdapter; 
        String strReturn;

        strPrefix = BuildPrefix(DataType);
        strSuffix = BuildSuffix(DataType);

        strSQL = "SELECT " + ReturnField + " FROM " + TableName + " WHERE " + FilterField + " LIKE " + strPrefix + FilterValue + strSuffix;
        myDataAdapter = DataAdapterFactory(strSQL, myConnection);
        myDataAdapter.Fill(myDataSet);

        //if (typeof(myDataAdapter) is SqlDataAdapter)
        //    ((SqlDataAdapter) myDataAdapter).Dispose();
        ////else if typeof myDataAdapter is MySql.Data.MySqlClient.MySqlDataAdapter Then
        ////    ((MySqlDataAdapter) myDataAdapter).Dispose();
        //else if (typeof(myDataAdapter) is OleDbDataAdapter)
        //    ((OleDbDataAdapter) myDataAdapter).Dispose();
        //else if (typeof(myDataAdapter) is OdbcDataAdapter)
        //    ((OdbcDataAdapter) myDataAdapter).Dispose();
        if(myDataSet.Tables[0].Rows.Count != 0)
            strReturn = myDataSet.Tables[0].Rows[0][ReturnField].ToString();
        else
            strReturn = "";

        return strReturn;
    }

    private IDbConnection ConnectionFactory()
    {
        IDbConnection myConnection;
        if (m_strConnectionType.ToLower().Equals("sqlconnection"))
            myConnection = new SqlConnection(m_strConnectionString);
        else if (m_strConnectionType.ToLower().Equals("odbcconnection"))
            myConnection = new OdbcConnection(m_strConnectionString);
        //else if (m_strConnectionType.ToLower().Equals("mysqlconnection"))
        //    myConnection = new MySql.Data.MySqlClient.MySqlConnection(m_strConnectionString);
        else
            myConnection = new OleDbConnection(m_strConnectionString);
        
        return myConnection;
    }

    private IDbCommand CommandFactory()
    {
        IDbCommand myCommand; 
        if(m_strConnectionType.ToLower().Equals("sqlconnection"))
            myCommand = new SqlCommand();
        else if (m_strConnectionType.ToLower().Equals("odbcconnection"))
            myCommand = new OdbcCommand();
        //else if(m_strConnectionType.ToLower().Equals("mysqlconnection"))
        //    myCommand = New MySql.Data.MySqlClient.MySqlCommand
        else
            myCommand = new OdbcCommand();

        return myCommand;
    }

    private IDbDataAdapter DataAdapterFactory(String SQL, IDbConnection Connection)
    {
        IDbDataAdapter myDataAdapter;
            if(m_strConnectionType.ToLower().Equals("sqlconnection"))
                myDataAdapter = new SqlDataAdapter(SQL, (SqlConnection)Connection);
            else if(m_strConnectionType.ToLower().Equals("odbcconnection"))
                myDataAdapter = new OdbcDataAdapter(SQL, (OdbcConnection)Connection);
            //else if(m_strConnectionType.ToLower().Equals("mysqlconnection"))
            //    myDataAdapter = New MySql.Data.MySqlClient.MySqlDataAdapter(strSQL, CType(myConnection, MySql.Data.MySqlClient.MySqlConnection))
            else
                myDataAdapter = new OleDbDataAdapter(SQL, (OleDbConnection)Connection);

            return myDataAdapter;
    }

    private String BuildPrefix(DataType DataType)
    {
        String strReturn = "";
        if (DataType == DataType.String)
            strReturn = "'";
        else if (DataType == DataType.Date)
            if(m_strDatabase.Equals("MSSQL"))
                strReturn = "'";
            else if (m_strDatabase.Equals("MSACCESS"))
                strReturn = "CDATE('";
            else
                strReturn = "";
        else
            strReturn = "";

            return strReturn;
    }

    private String BuildSuffix(DataType DataType)
    {
        String strReturn = "";
        if (DataType == DataType.String)
            strReturn = "'";
        else if (DataType == DataType.Date)
            if (m_strDatabase.Equals("MSSQL"))
                strReturn = "'";
            else if (m_strDatabase.Equals("MSACCESS"))
                strReturn = "')";
            else
                strReturn = "";
        else
            strReturn = "";

        return strReturn;
    }
}
public enum DataType
{
    String,
    Number,
    Date
}
